SQL Server 2012, IIS 7.0, .Net 4.5

Everything is fine when I stream a document on my dev machine (IISExpress), but on the staging server I get

System.ComponentModel.Win32Exception (0x80004005): Access is denied
   at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)
   at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)

Kerberos with delegation is set up between the staging IIS and SQL Server, database queries work fine on the staging server and integrated security works - I can see in sql profiler that I am getting to the sql server with my Active Directory credentials and I am set up as sysadmin for now. So I am wondering, what could possibly go wrong with the filestream access:

- Sql server and my account are set up fine, otherwise it would not work on my dev machine

- The staging server uses a valid conection to the sql server, otherwise I would not see my NT user name in profiler

- It can not be a two hop problem, since the same error pops up when I request the page from IE running on the staging server

- My C# code is very likely not the problem source, since I tried an additional coding example from MSDN and I am getting the same security exception with that (but the MSDN code also works on my dev machine)

I read a number of similar questions in the forums but typically an sql server login was the problem (which is not used in my scenario), and many such questions stayed unanswered. I also read that NTFS permissioning could be the problem but I don't think so since only the sql server service account needs access to the files (which it has, otherwise I would never be able to upload and download files).

Hello,

Did you specify SQL security to connect to database in your connection string? If so, please try to use integrated security. FILESTREAM requires integrated security. SQL Server authentication is not supported.

Please refer to Bob Dorr's explanation in the following blog:
 How It Works: File Streams Requires Integrated Security (Windows Authentication)

Regards,
Fanny Liu

If you have any feedback on our support, please click here.

Thanks Liu. Yes, integrated security is being used and it is working for sql server operations. Also, since we are using two hops, Kerberos with delegation is being used.

Any other possible reasons for the "Access denied" error?

Hello<o:p></o:p>

I have the same problem.<o:p></o:p>

I have several computers running on different OS, and on XP, Win 7 x64 and Win8 x64 code is executing normally<o:p></o:p>

My problem is on 32 bit Win 7 x86, where same error appears<o:p> </o:p>

Hope my comment is helpful for solving the case

<o:p>Best Regards </o:p>

DK<o:p></o:p>

Hi DK, which machine is 32bit Win 7 x86? The IIS server? Our machines - clients and servers -are all 64bit, so that can't be it in our scenario. However, it would be interesting to see if your problem has similar causes.

This is still an issue for us. We are now using T-SQL to get data from the varbinary() column, so the true filestream advantage is gone.

I had this exact same issue, and I was able to get it working by granting SELECT and UPDATE rights to the FILESTREAM column.  Here is the T-SQL:

GRANT SELECT ON OBJECT::[schema name].[table name] TO [the role/user to grant the rights to] AS [dbo];
GO

GRANT UPDATE ON OBJECT::[schema name].[table name]([file stream column name]) TO [the role/user to grant the rights to] AS [dbo];
GO